/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[fnIO_GetFiles]
#-- Purpose:		Returns a list of files in a directory, and optionally
#--                 all sub-directories
#--	Last Update:	08/02/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
using System.IO;

public partial class UserDefinedFunctions
{
    // http://www.mssqltips.com/sqlservertip/2582/introduction-to-sql-server-clr-table-valued-functions/
    private class FileProperties
    {
        public SqlString FileName;
        public SqlInt64 FileSize;
        public SqlDateTime CreationTime, LastWriteTime;

        public FileProperties(SqlString fileName, SqlInt64 fileSize, SqlDateTime creationTime, SqlDateTime lastWriteTime)
        {
            FileName = fileName;
            FileSize = fileSize;
            CreationTime = creationTime;
            LastWriteTime = lastWriteTime;
        }
    }

    // http://nlsimmons.com/SQLScraps/?p=71

    [Microsoft.SqlServer.Server.SqlFunction(
        Name = "fnIO_GetFiles",
        FillRowMethodName = "fnIO_GetFiles_FillRow",
        TableDefinition = "physical_device_name nvarchar(4000), create_date datetime, last_write_date datetime, file_size bigint")]
    public static IEnumerable fnIO_GetFiles(string directoryPath, bool recursive)
    {
        ArrayList resultItems = new ArrayList();

        if (System.IO.Directory.Exists(directoryPath))
            foreach (string filePath in System.IO.Directory.GetFiles(directoryPath, "*", (recursive ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)))
            {
                FileInfo fi = new FileInfo(filePath);
                resultItems.Add(new FileProperties(filePath, fi.Length, fi.CreationTime, fi.LastWriteTime));
            }

        return resultItems;
    }
    public static void fnIO_GetFiles_FillRow(Object obj, out SqlString filePath, out SqlDateTime createDate, out SqlDateTime lastWriteDate, out SqlInt64 fileSize)
    {
        FileProperties item = (FileProperties)obj;
        filePath = item.FileName;
        createDate = item.CreationTime;
        lastWriteDate = item.LastWriteTime;
        fileSize = item.FileSize;
    }
}
